﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using OgilvyOne.Model;
using OgilvyOne.IDAL;
using OgilvyOne.Utility;

namespace OgilvyOne.SqlServerDAL
{
    public class CmsArticleImgs : ICmsArticleImgs
    {

        private const string ADD_SQL = "insert into CMS_ARTICLE_IMGS(IMG_NAME,IMG_TYPE,IMG_FILE_NAME,ARTICLE_ID,IMG_ORDER,STATE,ADD_TIME,USER_ID,IMG_SMALL_FILE_NAME,IMG_LIST_FILE_NAME) values(@IMG_NAME,@IMG_TYPE,@IMG_FILE_NAME,@ARTICLE_ID,@IMG_ORDER,@STATE,getDate(),@USER_ID,@IMG_SMALL_FILE_NAME,@IMG_LIST_FILE_NAME);select @@IDENTITY";
        //添加
        public int Add(CmsArticleImgModel model)
        {

            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@IMG_NAME", SqlDbType.VarChar,50),
                new SqlParameter("@IMG_TYPE",SqlDbType.Int,4),
                new SqlParameter("@IMG_FILE_NAME", SqlDbType.VarChar,50),
                new SqlParameter("@ARTICLE_ID",SqlDbType.Int,4),
                new SqlParameter("@IMG_ORDER",SqlDbType.Int,4),
                new SqlParameter("@STATE",SqlDbType.Int,4),
                new SqlParameter("@USER_ID",SqlDbType.Int,4),
                new SqlParameter("@IMG_SMALL_FILE_NAME", SqlDbType.VarChar, 50),
                new SqlParameter("@IMG_LIST_FILE_NAME", SqlDbType.VarChar, 50)
            };
            parms[0].Value = model.ImgName;
            parms[1].Value = model.ImgType;
            parms[2].Value = model.ImgFileName;
            parms[3].Value = model.ArticleId;
            parms[4].Value = model.ImgOrder;
            parms[5].Value = model.State;
            parms[6].Value = model.UserId;
            parms[7].Value = model.ImgSmallFileName;
            parms[8].Value = model.Img_list_file_name;
            

            int id = 0;
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        string s = SQLHelper.ExecuteScalar(trans, CommandType.Text, ADD_SQL, parms).ToString();

                        id = int.Parse(s);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }

            return id;
        }

        private const string UPDATE_SQL = "update CMS_ARTICLE_IMGS set IMG_NAME=@IMG_NAME,IMG_TYPE=@IMG_TYPE,IMG_FILE_NAME=@IMG_FILE_NAME,IMG_ORDER=@IMG_ORDER,STATE=@STATE,IMG_SMALL_FILE_NAME=@IMG_SMALL_FILE_NAME,IMG_LIST_FILE_NAME=@IMG_LIST_FILE_NAME where IMGS_ID=@IMGS_ID";
        //修改基本资料
        public bool Update(CmsArticleImgModel model)
        {
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@IMG_NAME", SqlDbType.VarChar,50),
                new SqlParameter("@IMG_TYPE",SqlDbType.Int,4),
                new SqlParameter("@IMG_FILE_NAME", SqlDbType.VarChar,50),
                
                new SqlParameter("@IMG_ORDER",SqlDbType.Int,4),
                new SqlParameter("@STATE",SqlDbType.Int,4),
                new SqlParameter("@IMGS_ID",SqlDbType.Int,4),
                new SqlParameter("@IMG_SMALL_FILE_NAME", SqlDbType.VarChar, 50),
                new SqlParameter("@IMG_LIST_FILE_NAME", SqlDbType.VarChar, 50)
                
            };
            parms[0].Value = model.ImgName;
            parms[1].Value = model.ImgType;
            parms[2].Value = model.ImgFileName;
            
            parms[3].Value = model.ImgOrder;
            parms[4].Value = model.State;
            parms[5].Value = model.ImgsId;
            parms[6].Value = model.ImgSmallFileName;
            parms[7].Value = model.Img_list_file_name;
            
            
            int i = SQLHelper.ExecuteSql(UPDATE_SQL, parms);
            if (i > 0) return true;
            return false;
        }
        private const string GET_IMG_SQL = "select IMGS_ID,IMG_NAME,IMG_TYPE,IMG_FILE_NAME,ARTICLE_ID,IMG_ORDER,STATE,ADD_TIME,USER_ID,IMG_SMALL_FILE_NAME,IMG_LIST_FILE_NAME from CMS_ARTICLE_IMGS where IMGS_ID=@IMGS_ID";
        public CmsArticleImgModel GetImgModel(int iid)
        {
            CmsArticleImgModel mim = null;
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@IMGS_ID", SqlDbType.Int, 4)
                
            };
            parms[0].Value = iid;

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, GET_IMG_SQL, parms))
            {
                if (rdr.Read())
                {
                    mim = new CmsArticleImgModel();
                    mim.ImgsId = rdr.GetInt32(0);
                    mim.ImgName = rdr.GetString(1);
                    mim.ImgType = rdr.GetInt32(2);
                    mim.ImgFileName = rdr.GetString(3);

                    mim.ArticleId = rdr.GetInt32(4);
                    if (!rdr.IsDBNull(5)) mim.ImgOrder = rdr.GetInt32(5);
                    mim.State = rdr.GetInt32(6);
                    mim.Add_time = rdr.GetDateTime(7);
                    mim.UserId = rdr.GetInt32(8);
                    if (!rdr.IsDBNull(9)) mim.ImgSmallFileName = rdr.GetString(9);
                    if (!rdr.IsDBNull(10)) mim.Img_list_file_name = rdr.GetString(10);
                }
            }
            return mim;
        }

        public IList GetImgsList(CmsArticleImgModel q)
        {
            string qsql = "select IMGS_ID,IMG_NAME,IMG_TYPE,IMG_FILE_NAME,ARTICLE_ID,IMG_ORDER,STATE,ADD_TIME,USER_ID from CMS_ARTICLE_IMGS where 1=1 ";

            if (q != null)
            {
                if (q.ArticleId > 0 )
                {
                    qsql += " and ARTICLE_ID=" + q.ArticleId;
                }
                if (q.ImgName != null && q.ImgName != "")
                {
                    qsql += " and IMG_NAME like '%" + StringUtil.ReplaceSecStr(q.ImgName) + "%'";
                }
            }
            qsql += " order by IMG_ORDER,ADD_TIME desc";
            IList datalist = new ArrayList();
            SqlParameter[] parms = new SqlParameter[] { };

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, qsql, parms))
            {
                while (rdr.Read())
                {
                    CmsArticleImgModel mim = new CmsArticleImgModel();

                    mim = new CmsArticleImgModel();
                    mim.ImgsId = rdr.GetInt32(0);
                    mim.ImgName = rdr.GetString(1);
                    mim.ImgType = rdr.GetInt32(2);
                    mim.ImgFileName = rdr.GetString(3);

                    mim.ArticleId = rdr.GetInt32(4);
                    if (!rdr.IsDBNull(5)) mim.ImgOrder = rdr.GetInt32(5);
                    mim.State = rdr.GetInt32(6);
                    mim.Add_time = rdr.GetDateTime(7);
                    mim.UserId = rdr.GetInt32(8);
                    datalist.Add(mim);

                }
            }
            return datalist;
        }

        

        public bool Delete(int iid)
        {
            int i = SQLHelper.ExecuteSql("delete from CMS_ARTICLE_IMGS where IMGS_ID=" + iid, new SqlParameter[] { });
            if (i > 0) return true;
            return false;
        }


        //排序 s

        /// <summary>
        /// 修改排序号
        /// </summary>
        /// <param name="aid"></param>
        /// <param name="orderid"></param>
        /// <returns></returns>
        public bool ModOrderNum(int id, int ordernum)
        {
            bool re = false;
            string usql = "update CMS_ARTICLE_IMGS set IMG_ORDER = " + ordernum + " where IMGS_ID=" + id;
            int i = SQLHelper.ExecuteSql(usql, new SqlParameter[] { });
            if (i > 0)
            {
                re = true;
            }
            return re;
        }

        public int GetOrderImgsId(int aid, int ordernum, int next)
        {
            int reaid = 0;
            string ddd = ">";
            string ot = "";
            if (next == 0)
            {
                ddd = "<";
                ot = " desc";
            }
            string sql = "select top 1 IMGS_ID from CMS_ARTICLE_IMGS where IMG_ORDER" + ddd + ordernum + " and ARTICLE_ID=" + aid + " order by IMG_ORDER " + ot;

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql, new SqlParameter[] { }))
            {
                if (rdr.Read())
                {
                    reaid = rdr.GetInt32(0);
                }

            }
            return reaid;
        }

        private const string MAX_ORDER_SQL = "select max(IMG_ORDER) from CMS_ARTICLE_IMGS c where c.ARTICLE_ID=@aid ";
        /// <summary>
        /// 栏目文章最大排序值
        /// </summary>
        /// <param name="cid"></param>
        /// <returns></returns>
        public int MaxOrder(int aid)
        {
            int oid = 0;
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@aid", SqlDbType.Int, 4)
            };
            parms[0].Value = aid;
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, MAX_ORDER_SQL, parms))
            {
                if (rdr.Read())
                {
                    if (!rdr.IsDBNull(0))
                    {
                        oid = rdr.GetInt32(0);
                    }
                }
            }
            return oid;
        }

        //排序 e

    }
}
